package demo.dao;


import demo.entity.employees;
import demo.entity.managers;
import demo.entity.record;
import demo.mappers.empRowMapper;
import demo.mappers.managersRowMapper;
import demo.mappers.recordRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;


import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class CommonDao {       ///////还要改离岗签到的东西

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public employees getInfo(int emp_id){    //根据emp_ip返回employees实例
        String sql = "SELECT * FROM employees WHERE emp_id = ? ";
        return jdbcTemplate.queryForObject(sql,new empRowMapper(), emp_id);
    }

    public record Commonreturn(employees emp) {    //正常签到
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd ");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
        String nowdate = dateFormat.format(date);
        String nowtime = timeFormat.format(date);
        String sql2 = "INSERT INTO record VALUES (?,?,?,?,?,?,?,?,?)";
        String onTime="上班正常";
        jdbcTemplate.update(sql2,null,emp.getEmp_id(),emp.getName(),emp.getDepartment_id(),emp.getJob_id(),nowtime,null,onTime,nowdate);
        String sql3 = "SELECT * FROM record WHERE arrival_time=? AND date=? ";
        return jdbcTemplate.queryForObject(sql3,new recordRowMapper(), nowtime,nowdate);
    }
    public record OffWorkCommonReturn(employees emp) {    //正常离岗
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd ");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
        String nowdate = dateFormat.format(date);
        String nowtime = timeFormat.format(date);
        String sql = "SELECT * FROM record WHERE emp_id=? ";
        String getStatus = jdbcTemplate.queryForObject(sql,new recordRowMapper(), emp.getEmp_id()).getStatus();
        String newStatus=getStatus+"+下班正常";
        String sql1 = "UPDATE record set departure_time=?,status=? WHERE emp_id=? AND date = ?";
        jdbcTemplate.update(sql1,nowtime,newStatus,emp.getEmp_id(),nowdate);
        String sql2 = "SELECT * FROM record WHERE emp_id=? AND date = ? ";
        return jdbcTemplate.queryForObject(sql2,new recordRowMapper(), emp.getEmp_id(), nowdate);
    }
    public record Reducer(employees emp){      //迟到使本月工资-100更新employees表
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd ");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
        String nowdate = dateFormat.format(date);
        String nowtime = timeFormat.format(date);
        String sql = "UPDATE employees set monthly_salary=? WHERE emp_id=?";
        jdbcTemplate.update(sql,emp.getMonthly_salary(),emp.getEmp_id());
        String sql2 = "INSERT INTO record VALUES (?,?,?,?,?,?,?,?,?)";
        String shangbanchidao="上班迟到";
        jdbcTemplate.update(sql2,null,emp.getEmp_id(),emp.getName(),emp.getDepartment_id(),emp.getJob_id(),nowtime,null,shangbanchidao,nowdate);
        String sql3 = "SELECT * FROM record WHERE arrival_time=? AND date=? ";
        return jdbcTemplate.queryForObject(sql3,new recordRowMapper(), nowtime,nowdate);
    }
    public record OffWorkReducer(employees emp){      //早退使本月工资-100更新employees表
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd ");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
        String nowdate = dateFormat.format(date);
        String nowtime = timeFormat.format(date);
        String sql0 = "UPDATE employees set monthly_salary=? WHERE emp_id=?";
        jdbcTemplate.update(sql0,emp.getMonthly_salary(),emp.getEmp_id());
        String sql = "SELECT * FROM record WHERE emp_id=? AND date =?";
        String getStatus = (jdbcTemplate.queryForObject(sql,new recordRowMapper(), emp.getEmp_id(), nowdate)).getStatus();
        String newStatus=getStatus+"+下班早退";
        String sql1 = "UPDATE record set departure_time=?,status=?WHERE emp_id=? AND date =?";
        jdbcTemplate.update(sql1,nowtime,newStatus,emp.getEmp_id(), nowdate);
        String sql2 = "SELECT * FROM record WHERE emp_id=? AND date =?";
        return jdbcTemplate.queryForObject(sql2,new recordRowMapper(), emp.getEmp_id(),nowdate);
    }
    public List<record> queryRecord(int emp_id) {
        String sql = "SELECT * FROM record WHERE emp_id = ? ";
        return jdbcTemplate.query(sql, new recordRowMapper(), emp_id);
    }
    public managers addManagers(managers man) {
        Date date = new Date();
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateTime = dateTimeFormat.format(date);
        String sql = "INSERT INTO managers  VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        String weichuli=" 未处理 ";
        jdbcTemplate.update(sql, null, man.getEmp_id(),man.getName(),man.getDepartment_id(),man.getJob_id(),man.getReason(),weichuli,man.getStart_date(),man.getEnd_date(),dateTime,null);
        String sql2 = "SELECT * FROM managers WHERE apply_time = ? ";
        return jdbcTemplate.queryForObject(sql2, new managersRowMapper(),dateTime);
    }
    public List<managers> queryManagers(int emp_id){
        String sql = "SELECT * FROM managers WHERE emp_id = ? ";
        List<managers> man = jdbcTemplate.query(sql, new managersRowMapper(),emp_id);
        return man;
    }
    public managers worning() {
        managers worn = new managers();
        worn.setReason("剩余假期不足！");
        worn.setStatus("申请失败");
        return worn;
    }

}
